﻿--提取岗位异动一级审批列表
CREATE PROCEDURE [dbo].[proc_Employee_PostMove_GetApplyFList]
(
	@StartIndex nvarchar(20),
	@EndIndex nvarchar(20),
	@CompanyId nvarchar(400),
	@ename nvarchar(50),
	@audit nvarchar(4)
)
AS
DECLARE @sql NVARCHAR(4000)
DECLARE @swhere NVARCHAR(4000)
SET @swhere=' E.Ename like ''%'+@ename+'%'' and ee.Proposer is not null'
SET @swhere=@swhere+' and (case when ee.firstaudit=1 then 1 when ee.firstaudit=0 and ee.firstauditperson is null then 0'
SET @swhere=@swhere+' when ee.firstaudit=0 and ee.firstauditperson is not null then 2 end) like ''%'+@audit+'%'''	   
IF(@CompanyId<>'0')
SET @swhere=@swhere+' and EE.BCompanyid in('+@CompanyId+')'
SET @sql=
		+' ;WITH list As(Select ROW_NUMBER() OVER (Order By EE.firstaudit,EE.firstauditTime DESC)AS Row,'
		+' EE.Id,'
		+' (select ENumber from Employee E where EE.EId=E.Id) as ENumber,'
		+' (select ETempNumber from Employee E where EE.EId=E.Id) as ETempNumber,'
		+' EE.EId,'
		+' (select EName from Employee E where EE.EId=E.Id) as EName,'
		+' (select Sex from Employee E where EE.EId=E.Id) as Sex,'
		+' (case Acompanyname when '''' then (select CompanyName from Company where id=Acompanyid) else Acompanyname end) as CompanyName,'
		+' (case Bdepartmentname when '''' then (select DName from Department where id=Bdepartmentid) else Bdepartmentname end) as DName,'
		+' (case APostname when '''' then (select PostName from Post P where id=APostId) else APostname end) as PostName,'
		+' (case BPostName when '''' then (select PostName from Post P where id=BPostId) else BPostName end) as BPostName,'
		+' (case AGradename when '''' then (select Title from Grade G where id=ee.AGradeId) else AGradename end) as GradeName,'
		+' (case BGradeName when '''' then (select Title from Grade G where id=ee.BGradeId) else BGradeName end) as BGradeName,'
		+' EE.FirstAudit,EE.FirstAuditPerson,'
		+' (select EName from Employee E where EE.FirstAuditPerson=E.UserName) as FirstAuditPersonName,'
		+' EE.FirstAuditTime,EE.SecondAudit,EE.SecondAuditPerson,'
		+' (select EName from Employee E where EE.SecondAuditPerson=E.UserName) as SecondAuditPersonName,'
		+' EE.SecondAuditTime,IsNull(EE.ExecDate,''1900-01-01'') as ExecDate,'
		+' IsNull(EE.IsExec,0) as IsExec,E.GID'
		+' From employee_PostMove EE Inner Join Employee E on EE.EId=E.Id'
		+' Where '
		+@swhere
		+' )'
		+' Select *,(SELECT COUNT(0) FROM list) AS coun From list Where Row between '+ @StartIndex +' and '+ @EndIndex + ' Order By Row'
		
		EXEC(@sql)



----提取岗位异动一级审批列表
--ALTER proc [dbo].[proc_Employee_PostMove_GetApplyFList]
--(
--	@StartIndex int,
--	@EndIndex int,
--	@CompanyId int,
--	@ename nvarchar(50),
--	@audit nvarchar(4)
--)
--as
--	Begin
--		;WITH list As(Select ROW_NUMBER() OVER (Order By EE.Id ASC)AS Row,
--			EE.Id,
--			(select ENumber from Employee E where EE.EId=E.Id) as ENumber,
--			(select ETempNumber from Employee E where EE.EId=E.Id) as ETempNumber,
--			EE.EId,
--			(select EName from Employee E where EE.EId=E.Id) as EName,
--			(select Sex from Employee E where EE.EId=E.Id) as Sex,
--			(select CompanyName from Company where id=Acompanyid) as CompanyName,
--			(select DName from Department where id=bdepartmentid) as DName,
--			(select PostName from Post P where id=APostId) as PostName,
--			(select PostName from Post P where id=BPostId) as BPostName,
--			(select Title from Grade G where id=ee.AGradeId) as GradeName,			
--			EE.FirstAudit,
--			EE.FirstAuditPerson,
--			(select EName from Employee E where EE.FirstAuditPerson=E.UserName) as FirstAuditPersonName,
--			EE.FirstAuditTime,
--			EE.SecondAudit,
--			EE.SecondAuditPerson,
--			(select EName from Employee E where EE.SecondAuditPerson=E.UserName) as SecondAuditPersonName,
--			EE.SecondAuditTime,
--			IsNull(EE.ExecDate,'1900-01-01') as ExecDate,
--			IsNull(EE.IsExec,0) as IsExec
--			From employee_PostMove EE Inner Join Employee E on EE.EId=E.Id
--			Where 
--					(@CompanyId=0 Or EE.BCompanyid = @CompanyId)
--				And E.Ename like '%'+@ename+'%'
--				and ee.Proposer is not null
--				and (case when ee.firstaudit=1 then '1' 
--					   when ee.firstaudit=0 and ee.firstauditperson is null then '0'
--					   when ee.firstaudit=0 and ee.firstauditperson is not null then '1' end) like '%'+@audit+'%'
--				--and  e.state <> 5
--		)
--		Select *,(SELECT COUNT(0) FROM list) AS coun From list Where Row between @StartIndex and @EndIndex
--	End

RETURN
